package com.rockcent.mall.lib.repository;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.rockcent.common.common.SQLObjectDoing;
import com.rockcent.common.domain.enumclass.Whether;
import com.rockcent.mall.lib.domain.enumclass.ProductEventType;
import com.rockcent.mall.lib.dto.MerchantDto;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

/**
 * Created by zhangjh on 2016/9/29.
 */
@Repository
public class MerchantRepositoryImpl implements MerchantRepositoryCustomer {

    private static final Logger log = LoggerFactory.getLogger(MerchantRepositoryImpl.class);

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    @Autowired
    private ObjectMapper objectMapper;

    public MerchantDto getMechantInforById(Long merchantId) {
        SQLObjectDoing<MerchantDto> result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("SELECT t.mall_id,t.`id`,t.`name`,t.`customer_service_contact`" +
                    " ,t.`email`,t.`intro`,t.`merchant_register_type`,t.`status`,t.`prod_audit`,t1.`type`,t.face_url,t.logo_url" +
                    " FROM merchant t LEFT JOIN merchant_certify t1  ON t.`id`=t1.merchant_id");
            sql.append(" WHERE t.`id` = :merchantId");

            params.put("merchantId", merchantId);

            log.debug(sql.toString());
        };
        return result.go(MerchantDto.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public Long getViewCountYesterDay(Long mallId, Long merchantId, ProductEventType eventType, Whether isFormChannel) {
        SQLObjectDoing<Long> result = (sql, fields, params, typeReferenceMap) -> {

            sql.append("select count(1) from (select %s from product_event pe");
            sql.append(" where pe.event_type=:eventType");

            sql.append(" and pe.mall_id=:mallId");
            sql.append(" and pe.merchant_id=:merchantId");
            if (isFormChannel != null) {
                if (isFormChannel == Whether.YES) {
                    sql.append(" and pe.channel is not null");
                }
            }
            sql.append(" and DATE(pe.date_created)=CURDATE()-1");
            sql.append(" and pe.is_delete='NO')b");
            params.put("eventType", eventType.toString());
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            fields.append("distinct pe.buyer_id");
        };
        return result.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public Long getNewCustomerCount(Long mallId, Long merchantId) {
        /**
         * select count(distinct mo.customer_id) from merchant_order mo
         where mo.mall_id=1 and mo.merchant_id=1
         and mo.is_delete='NO'
         AND TO_DAYS( NOW()) - TO_DAYS(mo.date_created) <= 1
         and mo.customer_id not in(select distinct mo.customer_id from merchant_order where mo.date_created<date_sub(current_date(),interval 1 day));
         *
         */
        SQLObjectDoing<Long> result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select count(1) from(select %s from merchant_order mo");
            sql.append(" where mo.mall_id=:mallId and mo.merchant_id=:merchantId");
            sql.append(" and mo.is_delete='NO'");
            sql.append(" and DATE(mo.date_created)=CURDATE()-1");
            sql.append(" and mo.customer_id not in(select distinct o.customer_id from merchant_order o where DATE(o.date_created)<CURDATE()-1 and o.merchant_id=:merchantId and o.mall_id=:mallId))b");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            fields.append("distinct mo.customer_id");

        };


        return result.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public Long getNewCustomerOrderCount(Long mallId, Long merchantId) {
        /**
         *
         select count(1) from merchant_order where customer_id in(
         select distinct mo.customer_id from merchant_order mo
         where mo.mall_id=1 and mo.merchant_id=1
         and mo.is_delete='NO'
         AND TO_DAYS( NOW()) - TO_DAYS(mo.date_created) <= 1
         and mo.customer_id not in(select distinct mo.customer_id from merchant_order where mo.date_created<date_sub(current_date(),interval 1 day)));
         */

        SQLObjectDoing<Long> result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select count(1) from merchant_order where customer_id in(");
            sql.append("select %s from merchant_order mo");
            sql.append(" where mo.mall_id=:mallId and mo.merchant_id=:merchantId");
            sql.append(" and mo.is_delete='NO'");
            sql.append(" and DATE(mo.date_created)=CURDATE()-1");
            sql.append(" and mo.customer_id not in(select distinct o.customer_id from merchant_order o where DATE(o.date_created)<CURDATE()-1 and o.mall_id=:mallId and o.merchant_id=:merchantId))");
            sql.append("AND mall_id =:mallId");
            sql.append(" AND merchant_id =:merchantId");
            sql.append(" AND DATE(date_created) = CURDATE()- 1");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            fields.append("distinct mo.customer_id");

        };

        return result.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public Double getNewCustomerOrderTotal(Long mallId, Long merchantId) {
        SQLObjectDoing<Double> result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select sum(total_amount) from merchant_order where customer_id in(");
            sql.append("select %s from merchant_order mo");
            sql.append(" where mo.mall_id=:mallId and mo.merchant_id=:merchantId");
            sql.append(" and mo.is_delete='NO'");
            sql.append(" and DATE(mo.date_created)=CURDATE()-1");
            sql.append(" and mo.customer_id not in(select distinct o.customer_id from merchant_order o where DATE(o.date_created)<CURDATE()-1  and o.mall_id=:mallId and o.merchant_id=:merchantId))");
            sql.append(" and DATE(date_created)=CURDATE()-1");
            sql.append("  AND mall_id =:mallId");
            sql.append(" AND merchant_id =:merchantId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            fields.append("distinct mo.customer_id");

        };

        return result.go(Double.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public Long getOldCustomerOrderCount(Long mallId, Long merchantId) {
        SQLObjectDoing<Long> result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select count(1) from merchant_order where exists(");
            sql.append("select 1 from merchant_order mo");
            sql.append(" where mo.mall_id=:mallId and mo.merchant_id=:merchantId");
            sql.append(" and mo.is_delete='NO'");
            sql.append(" and DATE(mo.date_created)=CURDATE()-1");
            sql.append(" and exists(select 1 from merchant_order o where DATE(o.date_created)<CURDATE()-1" +
                    "  AND o.mall_id =:mallId  AND o.merchant_id =:merchantId and mo.customer_id=o.customer_id)");
            sql.append(" and DATE(date_created)=CURDATE()-1 and mo.customer_id=customer_id)");
            sql.append("  AND mall_id =:mallId");
            sql.append(" AND merchant_id =:merchantId");
            sql.append(" and DATE(date_created)=CURDATE()-1");


            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
        };

        return result.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public Double getOldCustomerOrderTotal(Long mallId, Long merchantId) {

        SQLObjectDoing<Double> result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select sum(total_amount) from merchant_order where exists(");
            sql.append("select 1 from merchant_order mo");
            sql.append(" where mo.mall_id=:mallId and mo.merchant_id=:merchantId");
            sql.append(" and mo.is_delete='NO'");
            sql.append(" and DATE(mo.date_created)=CURDATE()-1");
            sql.append(" and exists(select 1 from merchant_order o where DATE(o.date_created)<CURDATE()-1" +
                    " AND o.mall_id =:mallId  AND o.merchant_id =:merchantId and mo.customer_id=o.customer_id)");
            sql.append(" and DATE(date_created)=CURDATE()-1 and mo.customer_id=customer_id)");
            sql.append("  AND mall_id =:mallId");
            sql.append(" AND merchant_id =:merchantId");
            sql.append(" and DATE(date_created)=CURDATE()-1");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

        };

        return result.go(Double.class, jdbcTemplate, log, objectMapper);
    }

    @Override
    public Long getOldCustomerCount(Long mallId, Long merchantId) {

        SQLObjectDoing<Long> result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select count(1) from(select %s from merchant_order mo");
            sql.append(" where mo.mall_id=:mallId and mo.merchant_id=:merchantId");
            sql.append(" and mo.is_delete='NO'");
            sql.append(" and DATE(mo.date_created)=CURDATE()-1");
            sql.append(" and mo.customer_id  in(select distinct o.customer_id from merchant_order o where DATE(o.date_created)<CURDATE()-1" +
                    "  AND o.mall_id =:mallId AND o.merchant_id =:merchantId))b");

            params.put("mallId", mallId);
            params.put("merchantId", merchantId);

            fields.append("distinct mo.customer_id");

        };

        return result.go(Long.class, jdbcTemplate, log, objectMapper);
    }


}
